﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using UniversityStudentManagemnetSystem.DAO.DAL;

namespace UniversityStudentManagemnetSystem.DAO.Gateway
{
    public class EmployeeGateway:Gateway
    {
        
        Employee anEmployee=new Employee();
        public bool SaveEmployee(Employee aEmployee)
        {
            try
            {
                Connection.Open();
                string insertQuery =
                    string.Format(
                        "insert into t_Employee values (@EmployeeName,@DesignationId,@FatherName,@MotherName,@PresentAddress,@PermanentAddress,@NationalIdNumber,@EmailAddress,@ContactNumber,@PastExperience,@DateOfBirth,@JoiningDate,@ImageLocation)");
                Command.CommandText = insertQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@EmployeeName", SqlDbType.VarChar);
                Command.Parameters["@EmployeeName"].Value = aEmployee.Name;
                Command.Parameters.Add("@DesignationId", SqlDbType.Int);
                Command.Parameters["@DesignationId"].Value = aEmployee.Designation.EmployeeDesignationId;
                Command.Parameters.Add("@FatherName", SqlDbType.VarChar);
                Command.Parameters["@FatherName"].Value = aEmployee.FatherName;
                Command.Parameters.Add("@MotherName", SqlDbType.VarChar);
                Command.Parameters["@MotherName"].Value = aEmployee.MotherName;
                Command.Parameters.Add("@PresentAddress", SqlDbType.VarChar);
                Command.Parameters["@PresentAddress"].Value = aEmployee.PresentAaddres;
                Command.Parameters.Add("@PermanentAddress", SqlDbType.VarChar);
                Command.Parameters["@PermanentAddress"].Value = aEmployee.PermanentAaddres;
                Command.Parameters.Add("@NationalIdNumber", SqlDbType.VarChar);
                Command.Parameters["@NationalIdNumber"].Value = aEmployee.NationalIdNumber;
                Command.Parameters.Add("@EmailAddress", SqlDbType.VarChar);
                Command.Parameters["@EmailAddress"].Value = aEmployee.Email;
                Command.Parameters.Add("@ContactNumber", SqlDbType.VarChar);
                Command.Parameters["@ContactNumber"].Value = aEmployee.ContactNumber;
                Command.Parameters.Add("@PastExperience", SqlDbType.VarChar);
                Command.Parameters["@PastExperience"].Value = aEmployee.PastExperience;
                Command.Parameters.Add("@DateOfBirth", SqlDbType.DateTime);
                Command.Parameters["@DateOfBirth"].Value = aEmployee.DateOfBirth;
                Command.Parameters.Add("@JoiningDate", SqlDbType.DateTime);
                Command.Parameters["@JoiningDate"].Value = aEmployee.JoiningDate;
                Command.Parameters.Add("@ImageLocation", SqlDbType.VarChar);
                Command.Parameters["@ImageLocation"].Value = aEmployee.FileLocation;
                Command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception("Save couldn't performed due to system error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return true;
        }

        //public DataTable GetAllEmployeeFor(int selectedDesignation)
        //{
        //    try
        //    {
        //        string selectQuery =
        //            string.Format("select * from t_Employee where EmployeeDesignationId='" + selectedDesignation + "'");
        //        Command.CommandText = selectQuery;
        //        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
        //        DataSet dataSet = new DataSet();
        //        sqlDataAdapter.Fill(dataSet, "EmployeeTable");
        //        DataTable myDataTable = dataSet.Tables["EmployeeTable"];
        //        nbrOfTableRows = myDataTable.Rows.Count;
        //        return myDataTable;     
               
        //    }
        //    catch (Exception exception)
        //    {


        //        throw new Exception("System Error!", exception);
        //    }
        //    finally
        //    {
        //        if (Connection != null && Connection.State == ConnectionState.Open)
        //        {
        //            Connection.Close();
        //        }
        //    }
            
           
        //}

        //public DataTable GetAllEmployee()
        //{
        //    try
        //    {
        //        string selectQuery =
        //            string.Format("select * from t_Employee");
        //        Command.CommandText = selectQuery;
        //        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
        //        DataSet dataSet = new DataSet();
        //        sqlDataAdapter.Fill(dataSet, "EmployeeTable");
        //        DataTable myDataTable = dataSet.Tables["EmployeeTable"];
        //        nbrOfTableRows = myDataTable.Rows.Count;
        //        return myDataTable;

        //    }
        //    catch (Exception exception)
        //    {


        //        throw new Exception("System Error!", exception);
        //    }
        //    finally
        //    {
        //        if (Connection != null && Connection.State == ConnectionState.Open)
        //        {
        //            Connection.Close();
        //        }
        //    }

        //}

        public Employee GetEmployeeFor(string searchText)
        {

            try
            {
                Connection.Open();
                string selectQuery =
                    string.Format("SELECT  ed.Name, e.* FROM dbo.t_Employee as e INNER JOIN  dbo.t_EmployeeDesignation as ed ON e.Id = ed.Id where NationalIdNumber='" + searchText + "' or Email='" +
                                  searchText + "' or ContactNumber='" + searchText + "'");
                Command.CommandText = selectQuery;
                SqlDataReader reader = Command.ExecuteReader();
                while (reader.Read())
                {
                    anEmployee.Name = reader["EmployeeName"].ToString();
                    anEmployee.Designation.DesignationId = Convert.ToInt32(reader["EmployeeDesignationId"]);
                    anEmployee.Designation.DesignationName = reader["Name"].ToString();
                    anEmployee.FatherName = reader["FatherName"].ToString();
                    anEmployee.MotherName = reader["MotherName"].ToString();
                    anEmployee.PresentAaddres = reader["PresentAddress"].ToString();
                    anEmployee.PermanentAaddres = reader["PermanentAddress"].ToString();
                    anEmployee.NationalIdNumber = reader["NationalIdNumber"].ToString();
                    anEmployee.Email = reader["Email"].ToString();
                    anEmployee.ContactNumber = reader["ContactNumber"].ToString();
                    anEmployee.PastExperience = reader["PastExperience"].ToString();
                    anEmployee.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
                    anEmployee.JoiningDate = Convert.ToDateTime(reader["JoiningDate"]);
                }
            }
            catch (Exception exception)
            {
                
                throw new Exception("Sorry!System Error!",exception);
            }

            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return anEmployee;
        }

        public int GetPreviousEmployeeId()
        {
            int id = 0;
            try
            {
                Connection.Open();
                string selectQuery = string.Format("select Id from t_Employee");
                Command.CommandText = selectQuery;
                SqlDataReader sqlDataReader = Command.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    id = Convert.ToInt32(sqlDataReader["Id"]);
                }
            }
            catch (Exception exception)
            {
                throw new Exception("System Error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return id;
        }

        public bool HasThisNationalIdNumber(string nationalIdNumber)
        {
            bool hasRows = false;
            try
            {
                Connection.Open();
                string selectQuery = string.Format("select * from t_Employee where NationalIdNumber =@nationalIdNumber");
                Command.CommandText = selectQuery;
                Command.Parameters.Add("@nationalIdNumber", SqlDbType.VarChar);
                Command.Parameters["@nationalIdNumber"].Value = nationalIdNumber;
                Command.ExecuteNonQuery();
                SqlDataReader sqlDataReader = Command.ExecuteReader();
                if (sqlDataReader != null)
                {
                    hasRows = sqlDataReader.HasRows;
                }
            }
            catch (Exception exception)
            {
                throw new Exception("System Error!");
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return hasRows;
        }

        public bool HasThisEmail(string email)
        {
            bool hasRows = false;
            try
            {
                Connection.Open();
                string selectQuery = string.Format("select * from t_Employee where Email =@email");
                Command.CommandText = selectQuery;
                Command.Parameters.Add("@email", SqlDbType.VarChar);
                Command.Parameters["@email"].Value = email;
                Command.ExecuteNonQuery();
                SqlDataReader sqlDataReader = Command.ExecuteReader();
                if (sqlDataReader != null)
                {
                    hasRows = sqlDataReader.HasRows;
                }
            }
            catch (Exception exception)
            {
                throw new Exception("System Error!");
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return hasRows;
        }

        public List<Employee> GetAllEmployeeForSelectItem(int selectedDesignation)
        {
            List<Employee> employees=new List<Employee>();
            try
            {
                Connection.Open();
                string seslectQuery = string.Format("select * from t_Employee where EmployeeDesignationId='" + selectedDesignation + "'");
                Command.CommandText = seslectQuery;
                SqlDataReader reader = Command.ExecuteReader();
                while (reader.Read())
                {
                    
                    Employee aEmployee=new Employee();
                    aEmployee.Id = Convert.ToInt32(reader["Id"]);
                    aEmployee.Name = reader["EmployeeName"].ToString();
                    aEmployee.FatherName = reader["FatherName"].ToString();
                    aEmployee.MotherName = reader["MotherName"].ToString();
                    aEmployee.PresentAaddres = reader["PresentAddress"].ToString();
                    aEmployee.PermanentAaddres = reader["PermanentAddress"].ToString();
                    aEmployee.NationalIdNumber = (reader["NationalIdNumber"]).ToString();
                    aEmployee.Email = reader["Email"].ToString();
                    aEmployee.ContactNumber = reader["ContactNumber"].ToString();
                    employees.Add(aEmployee);
                }

            }
            catch (Exception exception)
            {
                
                throw new Exception(exception.Message);
            }
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return employees;
        }

       

        public Employee GetAllEmployeeFors(int id)
        {

            try
            {
                Connection.Open();
                string selectQuery =
                    string.Format("Select * from t_Employee where Id='"+id+"'");
                Command.CommandText = selectQuery;
                SqlDataReader reader = Command.ExecuteReader();
                while (reader.Read())
                {
                    anEmployee.Name = reader["EmployeeName"].ToString();
                    anEmployee.Designation.DesignationId = Convert.ToInt32(reader["EmployeeDesignationId"]);
                    anEmployee.FatherName = reader["FatherName"].ToString();
                    anEmployee.MotherName = reader["MotherName"].ToString();
                    anEmployee.PresentAaddres = reader["PresentAddress"].ToString();
                    anEmployee.PermanentAaddres = reader["PermanentAddress"].ToString();
                    anEmployee.NationalIdNumber = reader["NationalIdNumber"].ToString();
                    anEmployee.Email = reader["Email"].ToString();
                    anEmployee.ContactNumber = reader["ContactNumber"].ToString();
                }
            }
            catch (Exception exception)
            {

                throw new Exception("Sorry!System Error!", exception);
            }

            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return anEmployee;

            
        }

        public List<Employee> GetEmployeelist()
        {
            List<Employee> employees=new List<Employee>();
            try
            {

                Connection.Open();
                string selectQuery = string.Format("select * from t_Employee");
                Command.CommandText = selectQuery;
                SqlDataReader reader = Command.ExecuteReader();
                while (reader.Read())
                {
                    Employee aEmployee=new Employee();
                    aEmployee.Id = Convert.ToInt32(reader["Id"]);
                    aEmployee.Name = reader["EmployeeName"].ToString();
                    aEmployee.FatherName = reader["FatherName"].ToString();
                    aEmployee.MotherName = reader["MotherName"].ToString();
                    aEmployee.PresentAaddres = reader["PresentAddress"].ToString();
                    aEmployee.PermanentAaddres = reader["PermanentAddress"].ToString();
                    aEmployee.NationalIdNumber = (reader["NationalIdNumber"]).ToString();
                    aEmployee.Email = reader["Email"].ToString();
                    aEmployee.ContactNumber = reader["ContactNumber"].ToString();
                    employees.Add(aEmployee);
                }
            }
            catch (Exception exception)
            {
                
                throw new Exception(exception.Message);
            }
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return employees;
        }

        public bool UpdateEmployeeInformation(Employee aEmployee)
        {
            try
            {
                Connection.Open();
                string updateQuery = string.Format("UPDATE t_Employee SET EmployeeName=@employeeName,FatherName=@fatherName,MotherName = @motherName,PresentAddress=@presentAddress,PermanentAddress=@permanentAddress,Email=@email,@ContactNumber=@contactNumber WHERE Id = @id");
                Command.CommandText = updateQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@employeeName", SqlDbType.VarChar);
                Command.Parameters["@employeeName"].Value = aEmployee.Name;
                Command.Parameters.Add("@fatherName", SqlDbType.VarChar);
                Command.Parameters["@fatherName"].Value = aEmployee.FatherName;
                Command.Parameters.Add("@motherName", SqlDbType.VarChar);
                Command.Parameters["@motherName"].Value = aEmployee.MotherName;
                Command.Parameters.Add("@presentAddress", SqlDbType.VarChar);
                Command.Parameters["@presentAddress"].Value = aEmployee.PresentAaddres;
                Command.Parameters.Add("@permanentAddress", SqlDbType.VarChar);
                Command.Parameters["@permanentAddress"].Value = aEmployee.PermanentAaddres;
                Command.Parameters.Add("@email", SqlDbType.VarChar);
                Command.Parameters["@email"].Value = aEmployee.Email;
                Command.Parameters.Add("@contactNumber", SqlDbType.VarChar);
                Command.Parameters["@contactNumber"].Value = aEmployee.Email;

                Command.Parameters.Add("@id", SqlDbType.Int);
                Command.Parameters["@id"].Value = aEmployee.Id;
                Command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {

                throw new Exception("System Error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }

            }
            return true;
        }
    }
}